#!/usr/bin/env python
# encoding: utf-8
"""
socialParents_3WeeksPrior.py

Created by Brant Faircloth on 2007-06-12.
Copyright (c) 2007 Brant C. Faircloth. All rights reserved.

Determine social mate of females known to have laid eggs in a nest, for the 
breeding season, using telemetry data collected in the field.

"""

import string, MySQLdb, os, numpy, pdb

# make database connection
d = MySQLdb.connect("parameters")
c = d.cursor()
year = '2003'

def records():
    '''get records from ttrs_brood_genetics for each brood (id, parent, parent sex)'''
    
    q = (("SELECT brood, mother, m_sex, father, incubation, epp, incubator from ttrs_parents where mother IS NOT NULL AND EPP IS NOT NULL AND year(incubation) = '%s' ORDER BY mother") % (year))
    #q = (("SELECT brood1, parent2, parent2_sex, date from ttrs_%s_brood_genetics where brood1 = '111'") % (year))
    c.execute(q)
    data = c.fetchall()
    return data

def putativeParent(data):
    '''get associated birds of opposite sex from the mother of the brood 3 weeks prior to the broods incubation'''
    #pdb.set_trace()
    putativeParent = {}
    oldP = ''
    for parent in data:
        brood,id,sP,f,d,epp,inc = parent[0],parent[1],parent[2],parent[3],parent[4], parent[5], parent[6]
        # deal with bird_ids having multiple nests be appending nest count to bird_id if > first nest
        if oldP == id:
            i+=1
            id += '_' + str(i)
        else:
            oldP = id
            i=0
        q = (("SELECT assoc_bird1, assoc_bird2, assoc_bird3, assoc_bird4, assoc_bird5, assoc_bird6, assoc_bird7, assoc_bird8, assoc_bird9 FROM ttrs_telemetry WHERE bird_id = '%s' AND date between '%s-04-15' and '%s-10-15'") % (id,year,year))
        #pdb.set_trace()
        c.execute(q)
        assoc = c.fetchall()
        if id == None:
            pass
        #elif id == '010204':print brood
        else:
            info = {'brood':brood, 'parentSex':sP, 'father':f, 'date':d, 'epp':epp, 'inc':inc}
            putative = {}
            for row in assoc:
                for bid in row:
                    #if bid == '010204':pdb.set_trace()
                    try:
                        int(bid)
                        #pdb.set_trace()
                        if bid == None:
                            pass
                        else:
                            q = (("SELECT sex, capture_datetime from ttrs_capture where bird_id = '%s' ORDER BY capture_datetime DESC LIMIT 1") % (bid))
                            c.execute(q)
                            sex = c.fetchall()[0][0]
                            if sex != sP:
                                if bid not in putative.keys():
                                    putative[bid] = 1
                                else:
                                    putative[bid] += 1
                            else:
                                pass
                    except:
                        pass
            #pdb.set_trace()
            info['putative']=putative
            putativeParent[id] = info
    #pdb.set_trace()    
    return putativeParent

def write(file,putative):
    '''write out a list of these w/ the offspring id'''
    for b in putative:
        file.write(("%s,%s,%s,%s,%s,%s,%s,") % (putative[b]['brood'],putative[b]['inc'],b,putative[b]['date'],putative[b]['parentSex'],putative[b]['father'],putative[b]['epp']))
        stuffToSort = putative[b]['putative']
        #pdb.set_trace()
        # lexical sort by values
        ids     = numpy.array(stuffToSort.keys())
        counts  = numpy.array(stuffToSort.values())
        index   = numpy.lexsort(keys = (ids,counts))
        # resort keys
        ids     = ids[index]
        # resort values
        counts  = counts[index]
        revRange = range(len(ids))
        revRange.reverse()
        for p in revRange:
            file.write(("%s (%s),") % (ids[p],counts[p]))
        file.write("\n")
    file.close()
    
def main():
    data        = records()
    putative    = putativeParent(data)
    fileName    = (('%s_socialParentsCounts_BreedingSeason.txt') % (year))
    file        = open(fileName,'w')
    #pdb.set_trace()
    write(file,putative)

if __name__ == '__main__':
	main()